﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using SnailGame.Utility.DataAccess;
using System.Configuration;
using System.Collections;
using Com.Crc.PoJo.Model.Resource;
using System.Data;
using System.Data.OleDb;

namespace Com.Crc.ResManage.DAL.EquipManage
{
    public class ONUManagerDAL
    {
        DataBroker broker = new DataBroker();

        /// <summary>
        /// 删除设备，修改状态位为0
        /// </summary>
        /// <param name="wlanList"></param>
        /// <returns></returns>
        public bool DeleteONUData(ArrayList ONUList)
        {
            string id = "";
            for (int i = 0; i < ONUList.Count; i++)
            {
                id += ONUList[i] + ",";
            }
            id = id.Substring(0, id.Length - 1);
            string strSql = @"UPDATE TT_RESOURCE_ONU SET S_STATE='0' WHERE N_ID IN (" + id + ")";
            return broker.ExecuteForOleDB(strSql, ConfigurationManager.ConnectionStrings["CRCAppConn"].ToString());
        }

        /// <summary>
        /// 删除设备，修改状态位为0
        /// </summary>
        /// <param name="wlanList"></param>
        /// <returns></returns>
        public bool DeleteONUDataBy(ArrayList OLTList,ArrayList PONBoardList, ArrayList ONUList)
        {
            string strONUID = "";
            string strOLTID = "";
            string strPONBoardID = "";
            for (int i = 0; i < ONUList.Count; i++)
            {
                strONUID += ONUList[i] + ",";
            }
            strONUID = strONUID.Substring(0, strONUID.Length - 1);

            for (int i = 0; i < OLTList.Count; i++)
            {
                strOLTID += ONUList[i] + ",";
            }
            strOLTID = strOLTID.Substring(0, strOLTID.Length - 1);

            for (int i = 0; i < PONBoardList.Count; i++)
            {
                strPONBoardID += ONUList[i] + ",";
            }
            strPONBoardID = strPONBoardID.Substring(0, strPONBoardID.Length - 1);

            string strSql = @"UPDATE TT_RESOURCE_ONU SET S_STATE='0' WHERE 1=1 ";
            if (strONUID.Length > 0)
            {
                strSql += "AND N_ID IN (" + strONUID + ")";
            }
            if (strPONBoardID.Length > 0)
            {
                strSql += "AND N_ID IN (" + strPONBoardID + ")";
            }
            if (strOLTID.Length > 0)
            {
                strSql += "AND N_OLT_ID IN (" + strOLTID + ")";
            }
            return broker.ExecuteForOleDB(strSql, ConfigurationManager.ConnectionStrings["CRCAppConn"].ToString());
        }

        /// <summary>
        /// 修改ONU设备信息
        /// </summary>
        /// <param name="data">model实体</param>
        /// <returns></returns>
        public bool UpdateONUData(ONUEntity data)
        {
            string strSql = @" UPDATE TT_RESOURCE_ONU
                                  SET N_DEP_BRANCH_ID=" + data.N_DEP_BRANCH_ID + @",
                                      N_SUBSTATION_ID=" + data.N_SUBSTATION_ID + @",
                                      N_SUBDISTRICT_ID=" + data.N_SUBDISTRICT_ID + @",
                                      N_UNITTYPE_ID=" + data.N_UNITTYPE_ID + @",
                                      S_ADDRESS='" + data.S_ADDRESS + @"',
                                      S_MAC='" + data.S_MAC + @"',
                                      S_MANGER='" + data.S_MANGER + @"',
                                      N_VLAN=" + data.N_VLAN + @",
                                      N_OLT_ID=" + data.N_OLT_ID + @",
                                      N_USERBOARD_ID=" + data.N_USERBOARD_ID + @",
                                      N_PON_PORT=" + data.N_PON_PORT + @",
                                      N_S_IDX=" + data.N_S_IDX + @",
                                      N_E_IDX=" + data.N_E_IDX + @",
                                      S_BAK='" + data.S_BAK + @"',
                                      D_DATE='" + data.D_DATE.ToString("yyyy-MM-dd HH:mm:ss") + @"',
                                      S_STATE=" + data.S_STATE + @"
                                WHERE N_ID= " + data.N_ID;
            return broker.ExecuteForOleDB(strSql, ConfigurationManager.ConnectionStrings["CRCAppConn"].ToString());
        }

        /// <summary>
        /// 新增ONU设备信息
        /// </summary>
        /// <param name="data">data实体</param>
        /// <returns></returns>
        public bool AddONUData(ONUEntity data)
        {
            string strSql = @" INSERT INTO TT_RESOURCE_ONU(
                                        N_DEP_BRANCH_ID,
                                        N_SUBSTATION_ID,
                                        N_SUBDISTRICT_ID,
                                        N_UNITTYPE_ID,
                                        S_ADDRESS,
                                        S_MAC,
                                        S_MANGER,
                                        N_VLAN,
                                        N_OLT_ID,
                                        N_USERBOARD_ID,
                                        N_PON_PORT,
                                        N_S_IDX,
                                        N_E_IDX,
                                        S_BAK,
                                        D_DATE,
                                        S_STATE)
                                    VALUES (" + data.N_DEP_BRANCH_ID + "," +
                                                data.N_SUBSTATION_ID + "," +
                                                data.N_SUBDISTRICT_ID + "," +
                                                data.N_UNITTYPE_ID + "," +
                                                "'" + data.S_ADDRESS + "'" + "," +
                                                "'" + data.S_MAC + "'" + "," +
                                                "'" + data.S_MANGER + "'" + "," +
                                                "'" + data.N_VLAN + "'" + "," +
                                                data.N_OLT_ID + "," +
                                                data.N_USERBOARD_ID + "," +
                                                data.N_PON_PORT + "," +
                                                data.N_S_IDX + "," +
                                                data.N_E_IDX + "," +
                                                "'" + data.S_BAK + "'" + "," +
                                                "'" + data.D_DATE.ToString("yyyy-MM-dd HH:mm:ss") + "'," +
                                                "'" + data.S_STATE + "'"  +")";
            return broker.ExecuteForOleDB(strSql, ConfigurationManager.ConnectionStrings["CRCAppConn"].ToString());
        }
        /// <summary>
        /// 查询ONU设备信息
        /// </summary>
        /// <param name="strBranchID">分局ID</param>
        /// <param name="strOfficeID">局点ID</param>
        /// <param name="strSubDistrictID">小区ID</param>
        /// <param name="strEquipID">设备ID</param>
        /// <returns></returns>
        public DataTable QueryONUData(string strBranchID, string strOfficeID, string strSubDistrictID,string strEquipID)
        {
            string strSql = @" SELECT a.N_ID,
                                      a.N_SUBSTATION_ID,
                                      b.S_NAME AS S_SUBSTATION_NAME,
                                      a.N_DEP_BRANCH_ID,
                                      c.S_NAME AS S_BRANCH_NAME,
                                      a.N_SUBDISTRICT_ID,
                                      e.S_NAME AS S_SUBDISTRICT_NAME,
                                      a.N_UNITTYPE_ID,
                                      d.S_NAME AS S_ONU_TYPE,
                                      a.S_ADDRESS,
                                      a.S_MAC,
                                      a.S_MANGER,
                                      a.N_VLAN,
                                      a.N_OLT_ID,
                                      a.N_USERBOARD_ID,
                                      g.S_NAME AS S_OLT_NAME,
                                      a.N_PON_PORT,
                                      a.N_S_IDX,
                                      a.N_E_IDX,
                                      a.S_BAK,
                                      a.D_DATE,
                                      a.S_STATE 
                                 FROM TT_RESOURCE_ONU a
                           INNER JOIN TT_RESOURCE_SUBSTATION b ON a.N_SUBSTATION_ID = b.N_ID
                           INNER JOIN TT_RESOURCE_DICTIONARY c ON a.N_DEP_BRANCH_ID = c.N_ID AND c.S_TYPE=" + (int)Dictionary.COMMON_DIC_TYPE.RESOURCE_WLAN_BRANCH_TYPE + @"
                           INNER JOIN TT_RESOURCE_DICTIONARY d ON a.N_UNITTYPE_ID = d.N_ID AND d.S_TYPE=" + (int)Dictionary.COMMON_DIC_TYPE.RESOURCE_ONU_EQUIPMENT_TYPE + @"
                           INNER JOIN TT_RESOURCE_OLT g ON a.N_OLT_ID = g.N_ID
                           INNER JOIN TT_RESOURCE_SUBDISTRICT e ON a.N_SUBDISTRICT_ID = e.N_ID
                                WHERE a.S_STATE='1' ";
            if (strBranchID != "")
            {
                strSql += " AND a.N_DEP_BRANCH_ID = " + strBranchID;
            }
            if (strOfficeID != "")
            {
                strSql += " AND a.N_SUBSTATION_ID = " + strOfficeID;
            }
            if (strSubDistrictID != "")
            {
                strSql += " AND a.N_SUBDISTRICT_ID = " + strSubDistrictID;
            }
            if (strEquipID != "")
            {
                strSql += " AND a.N_ID = " + strEquipID;
            }
            return broker.ExecuteQueryForOleDB(strSql, ConfigurationManager.ConnectionStrings["CRCAppConn"].ToString());
        }

        /// <summary>
        /// 更新ONU端口信息
        /// </summary>
        /// <param name="data">model 实体</param>
        /// <returns></returns>
        public bool UpdateONUPortData(ONUPortEntity data)
        {
            string strSql = @" UPDATE TT_RESOURCE_ONU_PORT
                                  SET S_STATE='" + data.S_STATE + @"'
                                WHERE N_ID= " + data.N_ID;
            return broker.ExecuteForOleDB(strSql, ConfigurationManager.ConnectionStrings["CRCAppConn"].ToString());
        }

        /// <summary>
        /// 删除ONU端口信息
        /// </summary>
        /// <param name="data">model 实体</param>
        /// <returns></returns>
        public bool DeleteONUPortData(ArrayList ONUList)
        {
            string id = "";
            for (int i = 0; i < ONUList.Count; i++)
            {
                id += ONUList[i] + ",";
            }
            id = id.Substring(0, id.Length - 1);
            string strSql = @"UPDATE TT_RESOURCE_ONU_PORT SET S_STATE='0' WHERE N_ONU_ID IN (" + id + ")";
            return broker.ExecuteForOleDB(strSql, ConfigurationManager.ConnectionStrings["CRCAppConn"].ToString());
        }
        /// <summary>
        ///     自动分配端口
        /// </summary>
        /// <param name="onuId"></param>
        /// <param name="subdistrictId"></param>
        /// <returns></returns>
        public int usePort(int onuId, int subdistrictunitId)
        {
            OleDbParameter[] para = new OleDbParameter[]{
                new OleDbParameter("N_ONU_ID",OleDbType.Integer),
                new OleDbParameter("N_SUBDISTRICT",OleDbType.Integer),
                new OleDbParameter("N_ONU_PORT",OleDbType.Integer),
                new OleDbParameter("N_ERROR_CODE",OleDbType.Integer),
                new OleDbParameter("S_ERROR_INFO",OleDbType.Integer,255),
            };
            para[0].Value = onuId;
            para[1].Value = subdistrictunitId;
            para[2].Direction = ParameterDirection.Output;
            para[3].Direction = ParameterDirection.Output;
            para[4].Direction = ParameterDirection.Output;
            DataBroker broker = new DataBroker();

            broker.ExecuteProcedureForOleDB("ONU_USE_PORT", ConfigurationManager.ConnectionStrings["CRCAppConn"].ToString(), para);

            if ((int.Parse(Convert.ToString(para[3].Value))) != 1)
            {
                throw new Exception(Convert.ToString(para[4].Value));
            }
            if (Convert.ToString(para[4].Value).Equals(""))
            {
                return -1;
            }
            return (int.Parse(Convert.ToString(para[2].Value)));

        }
    }
}
